﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class ProductService : BaseService
    {
        public int Edit(int id, string name, string nameEn, int productCategoryId, string description, string descriptionEn, string model, bool isRecommend, int coverId, string keyword = "", string keywordEn = "", string finish = "", string finishEn = "", string material = "", string materialEn = "", string doorThickness = "", string backset = "", string availableFor = "", string availableForEn = "", string applicableStandard = "", string size = "", string modelEn = "")
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "产品名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "产品名称(En)");
            var sbSql = new StringBuilder();
            sbSql.Append("update tbproduct set ");
            sbSql.Append("CoName=@CoName");
            sbSql.Append(",");
            sbSql.Append("CoDescription=@CoDescription");
            sbSql.Append(",");
            sbSql.Append("CoProductCategoryId=@CoProductCategoryId");
            sbSql.Append(",");
            sbSql.Append("CoCategoryPath=@CoCategoryPath");
            sbSql.Append(",");
            sbSql.Append("CoModel=@CoModel");
            sbSql.Append(",");
            sbSql.Append("CoIsRecommend=@CoIsRecommend");
            sbSql.Append(",");
            sbSql.Append("CoCoverImageId=@CoCoverImageId");
            sbSql.Append(",");
            sbSql.Append("CoNormalImage=@CoNormalImage");
            sbSql.Append(",");
            sbSql.Append("CoSmallImage=@CoSmallImage");
            sbSql.Append(",");
            sbSql.Append("CoBigImage=@CoBigImage");
            sbSql.Append(",");
            sbSql.Append("CoNameEn=@CoNameEn");
            sbSql.Append(",");
            sbSql.Append("CoDescriptionEn=@CoDescriptionEn");
            sbSql.Append(",");
            sbSql.Append("CoKeyword=@CoKeyword");
            sbSql.Append(",");
            sbSql.Append("CoFinish=@CoFinish");
            sbSql.Append(",");
            sbSql.Append("CoMaterial=@CoMaterial");
            sbSql.Append(",");
            sbSql.Append("CoDoorThickness=@CoDoorThickness");
            sbSql.Append(",");
            sbSql.Append("CoBackset=@CoBackset");
            sbSql.Append(",");
            sbSql.Append("CoAvailableFor=@CoAvailableFor");
            sbSql.Append(",");
            sbSql.Append("CoApplicableStndard=@CoApplicableStndard");
            sbSql.Append(",");
            sbSql.Append("CoKeywordEn=@CoKeywordEn");
            sbSql.Append(",");
            sbSql.Append("CoFinishEn=@CoFinishEn");
            sbSql.Append(",");
            sbSql.Append("CoMaterialEn=@CoMaterialEn");
            sbSql.Append(",");
            sbSql.Append("CoAvailableForEn=@CoAvailableForEn");
            sbSql.Append(",");
            sbSql.Append("CoSize=@CoSize");
            sbSql.Append(",");
            sbSql.Append("CoModelEn=@CoModelEn");
            sbSql.Append(" where coproductId=@id");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescription", SqlDbType.VarChar),
                    new SqlParameter("@CoProductCategoryId", SqlDbType.VarChar),
                    new SqlParameter("@CoCategoryPath", SqlDbType.VarChar),
                    new SqlParameter("@CoModel", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),
                    new SqlParameter("@CoCoverImageId", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescriptionEn", SqlDbType.VarChar),
                    new SqlParameter("@CoKeyword", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoFinish", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoMaterial", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDoorThickness", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBackset", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAvailableFor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoApplicableStndard", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoKeywordEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoFinishEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoMaterialEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAvailableForEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSize", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoModelEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@id", SqlDbType.Int)
                };
            parameters[0].Value = name;
            parameters[1].Value = description;
            using (var sdr = DbHelperSql.ExecuteReader("select * from tbproductcategory where CoproductCategoryId={0}".FormatWith(productCategoryId)))
            {
                if (sdr.Read())
                {
                    parameters[2].Value = productCategoryId;
                    parameters[3].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    throw new Exception("分类不存在");
                }
            }
            parameters[4].Value = model;
            parameters[5].Value = isRecommend;
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbProductImage where CoProductImageId={0}".FormatWith(coverId)))
            {

                if (sdr.Read())
                {
                    parameters[6].Value = coverId;
                    parameters[7].Value = sdr["CoNormalImage"].SafeToString();
                    parameters[8].Value = sdr["CoSmallImage"].SafeToString();
                    parameters[9].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parameters[6].Value = DBNull.Value;
                    parameters[7].Value = DBNull.Value;
                    parameters[8].Value = DBNull.Value;
                    parameters[9].Value = DBNull.Value;
                }
            }

            parameters[10].Value = nameEn;
            parameters[11].Value = descriptionEn;
            parameters[12].Value = keyword;
            parameters[13].Value = finish;
            parameters[14].Value = material;
            parameters[15].Value = doorThickness;
            parameters[16].Value = backset;
            parameters[17].Value = availableFor;
            parameters[18].Value = applicableStandard;
            parameters[19].Value = keywordEn;
            parameters[20].Value = finishEn;
            parameters[21].Value = materialEn;
            parameters[22].Value = availableForEn;
            parameters[23].Value = size;
            parameters[24].Value = modelEn;
            parameters[25].Value = id;
            var rows = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt32();
            if (rows > 0)
            {
                DbHelperSql.ExecuteSqlTran(new ArrayList
                    {
                        "update TbProductImage set CoIsCover=0 where CoProductId={0} and CoIsCover=1".FormatWith(id),
                        "update TbProductImage set CoIsCover=1 where CoProductImageId={0}".FormatWith(coverId)

                    });
            }
            return rows;
        }
        public int Add(string name, string nameEn, int productCategoryId, string description, string descriptionEn, string model, bool isRecommend, int[] productImageIds, int coverId, string keyword = "", string keywordEn = "", string finish = "", string finishEn = "", string material = "", string materialEn = "", string doorThickness = "", string backset = "", string availableFor = "", string availableForEn = "", string applicableStandard = "", string size = "", string modelEn = "")
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "产品名称");
            Check.Argument.IsNotOutOfLength(nameEn, 50, "产品名称(En)");
            var strSqlOne = new StringBuilder();
            strSqlOne.Append("insert into tbproduct(");
            strSqlOne.Append("CoName,CoDescription,CoProductCategoryId,CoCategoryPath,CoModel,CoIsRecommend,CoCoverImageId,CoNormalImage,CoSmallImage,CoBigImage,CoNameEn,CoDescriptionEn,CoKeyword,CoFinish,CoMaterial,CoDoorThickness,CoBackset,CoAvailableFor,CoApplicableStndard,CoKeywordEn,CoFinishEn,CoMaterialEn,CoAvailableForEn,CoSize,CoModelEn");
            strSqlOne.Append(")");
            strSqlOne.Append(" values (");
            strSqlOne.Append("@CoName,@CoDescription,@CoProductCategoryId,@CoCategoryPath,@CoModel,@CoIsRecommend,@CoCoverImageId,@CoNormalImage,@CoSmallImage,@CoBigImage,@CoNameEn,@CoDescriptionEn,@CoKeyword,@CoFinish,@CoMaterial,@CoDoorThickness,@CoBackset,@CoAvailableFor,@CoApplicableStndard,@CoKeywordEn,@CoFinishEn,@CoMaterialEn,@CoAvailableForEn,@CoSize,@CoModelEn");
            strSqlOne.Append(")");
            SqlParameter[] parametersOne =
                {
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescription", SqlDbType.VarChar),
                    new SqlParameter("@CoProductCategoryId", SqlDbType.VarChar),
                    new SqlParameter("@CoCategoryPath", SqlDbType.VarChar),
                    new SqlParameter("@CoModel", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),
                    new SqlParameter("@CoCoverImageId", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoNameEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDescriptionEn", SqlDbType.VarChar),
                    new SqlParameter("@CoKeyword", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoFinish", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoMaterial", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoDoorThickness", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBackset", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAvailableFor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoApplicableStndard", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoKeywordEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoFinishEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoMaterialEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAvailableForEn", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSize", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoModelEn", SqlDbType.VarChar, 255)
                };
            parametersOne[0].Value = name;
            parametersOne[1].Value = description;
            using (var sdr = DbHelperSql.ExecuteReader("select * from tbproductcategory where CoproductCategoryId={0}".FormatWith(productCategoryId)))
            {

                if (sdr.Read())
                {
                    parametersOne[2].Value = productCategoryId;
                    parametersOne[3].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    throw new Exception("分类不存在");
                }
            }
            parametersOne[4].Value = model;
            parametersOne[5].Value = isRecommend;

            using (var sdr = DbHelperSql.ExecuteReader("select * from TbProductImage where CoProductImageId={0}".FormatWith(coverId)))
            {

                if (sdr.Read())
                {
                    parametersOne[6].Value = coverId;
                    parametersOne[7].Value = sdr["CoNormalImage"].SafeToString();
                    parametersOne[8].Value = sdr["CoSmallImage"].SafeToString();
                    parametersOne[9].Value = sdr["CoBigImage"].SafeToString();
                }
                else
                {
                    parametersOne[6].Value = DBNull.Value;
                    parametersOne[7].Value = DBNull.Value;
                    parametersOne[8].Value = DBNull.Value;
                    parametersOne[9].Value = DBNull.Value;
                }
            }
            parametersOne[10].Value = nameEn;
            parametersOne[11].Value = descriptionEn;
            parametersOne[12].Value = keyword;
            parametersOne[13].Value = finish;
            parametersOne[14].Value = material;
            parametersOne[15].Value = doorThickness;
            parametersOne[16].Value = backset;
            parametersOne[17].Value = availableFor;
            parametersOne[18].Value = applicableStandard;
            parametersOne[19].Value = keywordEn;
            parametersOne[20].Value = finishEn;
            parametersOne[21].Value = materialEn;
            parametersOne[22].Value = availableForEn;
            parametersOne[23].Value = size;
            parametersOne[24].Value = modelEn;
            var obj = DbHelperSql.Add(strSqlOne.ToString(), parametersOne).SafeToInt();
            if (obj > 0)
            {
                DbHelperSql.ExecuteSqlTran(new ArrayList
                    {
                        "update TbProductImage set CoProductId={0} where CoProductImageId in ({1}) and  exists(select * from tbproduct where coproductid={0})".FormatWith(obj,string.Join(",",productImageIds.Select(o =>o.ToString(CultureInfo.InvariantCulture)).ToArray())),
                        "update TbProductImage set CoIsCover=1 where CoProductImageId={0}".FormatWith(coverId)

                    });
            }
            return obj;
        }
        public void Delete(int id)
        {
            var path = HttpContext.Current.Server.MapPath("~/Upload/Product/");
            var ds = DbHelperSql.Query("select * from tbProductimage where CoProductId={0}".FormatWith(id));
            foreach (DataRow item in ds.Tables["ds"].Rows)
            {
                FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
            }
            DbHelperSql.ExecuteSql("delete from TbProduct where CoProductId={0}".FormatWith(id));
            DbHelperSql.ExecuteSql("delete from tbProductimage where CoProductId={0}".FormatWith(id));
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string name, string model, int productCategoryId, string isRecommend, int pageValue, int rowsValue)
        {
            var parameters = new[]
                             {
                                 new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                                 new SqlParameter("@CoModel", SqlDbType.VarChar, 255),
                                 new SqlParameter("@CoProductCategoryId", SqlDbType.Int),
                                 new SqlParameter("@CoIsRecommend",SqlDbType.Bit)
                             };
            parameters[0].Value = name.SqlLike();
            parameters[1].Value = model.SqlLike();
            parameters[2].Value = productCategoryId;
            parameters[3].Value = isRecommend.SafeToBool();
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where (A.CoName like '%'+@CoName+'%' or A.CoNameEn like '%'+@CoName+'%') and  (A.CoModel like '%'+@CoModel+'%'  or  A.CoModelEn like '%'+@CoModel+'%') ");
            if (productCategoryId != 0)
            {
                sbSqlWhere.Append(" and A.CoProductCategoryId=@CoProductCategoryId");
            }
            int total = DbHelperSql.GetSingle("select count(*) from TbProduct as A {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} A.CoProductId,A.CoName,A.CoNameEn,A.CoProductCategoryId,A.CoIsRecommend,A.CoNormalImage,A.CoSmallImage, B.CoName as CoCategoryName from TbProduct as A left join TbProductCategory as B on  A.CoProductCategoryId=B.CoProductCategoryId {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and CoProductId not in (select top {0} A.CoProductId from TbProduct as A {1} order by A.CoProductId) ", rowsValue * (pageValue - 1), sbSqlWhere);
            sbSql.Append(" order by A.CoProductId");
            var ds = DbHelperSql.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total,
                DataTable = ds.Tables["ds"]
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {

            var rows = DbHelperSql.Query("select *  from tbproduct  where CoProductId={0}".FormatWith(id)).Tables["ds"].Rows;
            return rows[0];
        }

    }
}
